package db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.security.InvalidParameterException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;

import org.hsqldb.Types;

import util.HashList;
import util.MyLogger;

/**
 * The DBObject provides some basic SQL-Statements for simple database 
 * access on table level .
 * 
 * @author Kristin Behrens, Robin Gawenda, Swen Jung
 * @see MyLogger
 * @see Connection
 * @see DBConnection
 * @see DBSQLUtil
 */
public abstract class DBObject extends DBSQLUtil {
	/**
	 * The String <i>table</i> represents the name of the table to be accessed.
	 */
	protected String tableName = null;

	/**
	 * All columns, their datatypes and restrictions as well as the default
	 * value.
	 */
	protected Object[][] default_columns = null;

	public static final int DEFAULT_COLUMN_NAME = 0, DEFAULT_COLUMN_TYPE = 1,
			DEFAULT_COLUMN_RESTRICTION = 2, DEFAULT_COLUMN_DEFAULT = 3;

	protected final int max_default_column_properties = 4;
	
	/**
	 * The database {@link Connection} object.
	 */
	protected Connection connection = null;

	/**
	 * The DBObject constructor. Creates a database {@link Connection} and
	 * assures that the table is existing, by calling the checkTable() method.
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	protected DBObject(String t, Object[][] columns)
			throws ClassNotFoundException, SQLException {
		if (columns[0].length != max_default_column_properties)
			throw new InvalidParameterException(
					"The given object[][] must be in "
							+ "second level of size "
							+ max_default_column_properties + " but is "
							+ default_columns[0].length + "!");
		default_columns = columns;
		tableName = t;
		connection = DBConnection.getConnection();
		checkTable();
	}
	
	/**
	 * Creates a view with the given columns and the statement
	 * @param t
	 * @param columns
	 * @param statement
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	protected DBObject(String t, Object[][] columns, String statement) throws ClassNotFoundException, SQLException{
		if (columns[0].length != max_default_column_properties)
			throw new InvalidParameterException(
					"The given object[][] must be in "
							+ "second level of size "
							+ max_default_column_properties + " but is "
							+ default_columns[0].length + "!");
		default_columns = columns;
		tableName = t;
		connection = DBConnection.getConnection();
		if(!existsView())createView(statement);
	}
	
	public String getAbsoluteColumn(String col){
		boolean contains=false;
		for(Object[] obj:default_columns){
			if(((String)obj[0]).equals(col))contains=true;
		}
		if(!contains) throw new IllegalArgumentException("The given column \""+col+"\" is not part of the table \""+tableName+"\"");
		return tableName+"."+col;
	}

	/**
	 * Prints the current TableMetaData via standard out.
	 */
	public void printTableMetaData(String name) {
		try {
			DatabaseMetaData dmb = connection.getMetaData();
			ResultSet rs = dmb.getTables(null, null, name.toUpperCase(),
					new String[] { "TABLE" });
			int max = rs.getMetaData().getColumnCount() + 1;
			for (int i = 1; i < max; i++) {
				System.out.print(rs.getMetaData().getColumnName(i) + " | ");
			}
			while (rs.next()) {
				for (int i = 1; i < max; i++) {
					System.out.print(rs.getString(i) + " | ");
				}
				System.out.println();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Used to check if the table exists.
	 * 
	 * @return true if exists
	 */
	protected boolean existsTable() {
		return existsStructure("TABLE");
	}
	
	/**
	 * Used to check if the table exists.
	 * 
	 * @return true if exists
	 */
	protected boolean existsView() {
		return existsStructure("VIEW");
	}
	
	/**
	 * Used to check if the databaseelement exists.
	 * 
	 * @return true if exists
	 */
	protected boolean existsStructure(String type) {
		DatabaseMetaData dmb = null;
		ResultSet rs = null;
		boolean exists = false;
		try {
			dmb = connection.getMetaData();
			rs = dmb.getTables(null, null, tableName.toUpperCase(),
					new String[] { type });
			exists = rs.isBeforeFirst();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		return exists;
	}

	/**
	 * Checks if all predefined columns are present.
	 * 
	 * @return true if correct
	 */
	protected boolean existColumns() {
		boolean correct = false;
		ResultSet rs = null;
		HashList<String> table_columns = new HashList<String>();
		for (int i = 0; i < default_columns.length; i++)
			table_columns
					.add(((String) default_columns[i][DEFAULT_COLUMN_NAME])
							.toLowerCase());

		try {
			rs = connection.prepareStatement(
					"SELECT * FROM " + tableName + ";",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int size = rsmd.getColumnCount() > default_columns.length ? rsmd
					.getColumnCount() : default_columns.length;
			correct = true; // if this far
			for (int i = 1; i <= size; i++) {
				if (!table_columns
						.contains(rsmd.getColumnName(i).toLowerCase())) {
					log
							.warning("The column " + rsmd.getColumnName(i)
									+ " is not present in the " + tableName
									+ " table!");
					correct = false;
				} else if (rsmd.getColumnType(i) != (Integer) default_columns[i - 1][DEFAULT_COLUMN_TYPE]) {
					log.warning("The column " + rsmd.getColumnName(i)
							+ " is present in the " + tableName
							+ " table but has the wrong data type!");
					correct = false;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			correct = false;
		}finally{
			closeResult(rs);
		}
		
		return correct;
	}

	/**
	 * Checks if the files table exists in the database and otherwise creates a
	 * new table with the given attributes. This method is called by the
	 * {@link DBObject} constructor.
	 */
	protected void checkTable() {
		if (existsTable()) {
			if (existColumns()) {
				log.fine("TABLE " + tableName + " exists.");
				/***************************************************************
				 * When everythings fine, the routine will stop and exit here.
				 */
			} else {
				try {
					createTableStructure();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		} else {
			/*******************************************************************
			 * This exception will be thrown if the called table does not exist
			 * and then trying to create the table.
			 */
			try {
				createTable();
				log.entering(sourceClass, "insertDefaultValues()");
				insertDefaultValues();
				log.exiting(sourceClass, "insertDefaultValues()");
			} catch (SQLException e) {
				/***************************************************************
				 * Anything that happens while trying to create the table ends
				 * up here.
				 */
				e.printStackTrace();
			}
		}
	}

	/**
	 * Returns all column names for the
	 * 
	 * @param createStatement
	 * @return
	 */
	protected String getAllColumns(boolean createStatement) {
		String str = "";
		for (int i = 0; i < default_columns.length; i++) {
			str += default_columns[i][DEFAULT_COLUMN_NAME] + " ";
			if (createStatement)
				str += Types
						.getTypeString((Integer) default_columns[i][DEFAULT_COLUMN_TYPE])
						+ " " + default_columns[i][DEFAULT_COLUMN_RESTRICTION];
			if (i < default_columns.length - 1)
				str += ",";
		}
		return str;
	}
	
	protected String[] getAllAbsoluteColumnsAsArray(){
		String[] str = new String[default_columns.length];
		for (int i = 1; i < default_columns.length; i++) {
			str[i]=tableName+"."+default_columns[i][DEFAULT_COLUMN_NAME] + " ";
		}
		return str;
	}
	
	public static String[] getAllAbsoluteColumnsAsArray(DBObject[] dbs){
		ArrayList<String> str =null;
		for(DBObject db:dbs){
			for(Object[]col:db.default_columns)
			str.add(db.tableName+"."+col[DEFAULT_COLUMN_NAME]);
		}
		return (String[])str.toArray();
	}

	protected void createTable() throws SQLException {
		String str = getAllColumns(true);
		String statement = "CREATE TABLE " + tableName + " (" + str + ");";
		PreparedStatement ps = connection.prepareStatement(statement);
		log.finest("CREATE-Statement executed: " + statement);
		ps.execute();
		ps.close();
		log.info("TABLE " + tableName + " was created");
	}

	public int dropTable() {
		int result=-1;
		PreparedStatement ps=null;
		try {
			ps = connection.prepareStatement("DROP TABLE "
					+ tableName + " CASCADE;");
			result=ps.executeUpdate();
			log.info("TABLE " + tableName + " dropped");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return result;
	}

	protected void createView(String statement) throws SQLException {
		//String str = getAllColumns(false);
		PreparedStatement ps = connection.prepareStatement("CREATE VIEW "
				+ tableName + " AS "+statement+";"); //" (" + str + ")"
		ps.execute();
		ps.close();
		log.info("VIEW " + tableName + " was created");
	}

	public int dropView() {
		int result=-1;
		PreparedStatement ps=null;
		try {
			ps = connection.prepareStatement("DROP VIEW "
					+ tableName + " CASCADE;");
			result=ps.executeUpdate();
			log.info("VIEW " + tableName + " dropped");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return result;
	}

	/**
	 * The returned Vector contains all data from the database, the first set is
	 * the table header. It consists of an two-dimensional object array with
	 * [column][0] being the SQL data {@link Types} and [column][1] the column
	 * name.
	 * 
	 * @return a Vector with
	 * @throws SQLException
	 */
	protected void createTableStructure() throws SQLException {
		ResultSet rs=selectAllAsResultSet();
		ArrayList<Object> header=getColumnNamesAsArrayList(rs);
		ArrayList<ArrayList<Object>> data = getRowsAsObjectArrayList(rs);
		String[] head=new String[header.size()];
		head=header.toArray(head);
		
		dropTable();
		createTable();
		
		rs=selectAllAsResultSet();
		try {
			if (rs == null)
				return;
			for(ArrayList<Object> obj:data) {
				insert(head, obj.toArray());
			}
		} catch (NullPointerException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		if(data.size()==0)insertDefaultValues();
	}
	
	protected static void updateValue(ResultSet rs, String col, int type, Object val) throws SQLException{
		switch (type) {
		case Types.BOOLEAN:
			rs.updateBoolean(col, (Boolean) val);
			break;
		case Types.INTEGER:
			rs.updateInt(col, (Integer) val);
			break;
		case Types.DOUBLE:
			rs.updateDouble(col, (Double) val);
			break;
		case Types.VARCHAR:
			rs.updateString(col, (String) val);
			break;
		case Types.CHAR:
			rs.updateString(col, (String) val);
			break;
		case Types.DATE:
			rs.updateDate(col, (Date) val);
			break;
		case Types.TIMESTAMP:
			rs.updateTimestamp(col, (Timestamp) val);
			break;
		case Types.BINARY:
			rs.updateBytes(col, (byte[]) val);
			break;
		case Types.VARBINARY:
			rs.updateBytes(col, (byte[]) val);
			break;
		case Types.LONGVARBINARY:
			rs.updateBytes(col, (byte[]) val);
			break;
		case Types.TINYINT:
			rs.updateByte(col, (Byte) val);
			break;
		case Types.TIME:
			rs.updateTime(col, (Time) val);
			break;
		default:
			rs.updateObject(col, (Object) val);
			break;
		}
	}

	/**
	 * Returns a {@link PreparedStatement} object based on the given SQL-String.
	 * 
	 * @param sqlString
	 * @return a {@link PreparedStatement} object
	 */
	public PreparedStatement prepareStatement(String sqlString) {
		try {
			return connection.prepareStatement(sqlString);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Inserts a new data set into the current database table.
	 * 
	 * @param columns -
	 *            the order of the columns
	 * @param values -
	 *            values to be inserted in the table.
	 */
	protected boolean insertColumns(String[] columns, Object[][] values) {
		boolean success=false;
		for(int i=0;i<values.length;i++)
			success&=(insert(columns, values[i])>0);
		return success;
	}
	
	public int insert(String column, Object value){
		return insert(new String[]{column},new Object[]{value});
	}

	/**
	 * Inserts a new data set into the current database table.
	 * 
	 * @param columns -
	 *            the order of the columns
	 * @param values -
	 *            values to be inserted in the table.
	 */
	public int insert(String[] columns, Object[] values) {
		PreparedStatement ps = null;
		int success=-1;
		
		if (columns.length == 0 || values.length == 0)
			throw new IllegalArgumentException(
					"Wrong type of given arrays. [No elements in arrays]");
		String stmt = "";
		String cols = "";
		for (int i = 0; i < columns.length; i++){
			cols += i==0?"":",";
			cols += columns[i];
		}
		String ins = "(";
		for (int j = 0; j < values.length; j++) {
			ins += j == 0 ? "" : ",";
			ins += "?";
		}
		ins += ")";
		
		stmt = "INSERT INTO " + tableName + " (" + cols + ") VALUES "
				+ ins + ";";
		ps=prepareStatement(stmt);
		try {
				for (int i = 0; i < values.length; i++) {
					DBSQLUtil.setValueInPreparedStatement(ps, values[i], i+1);
				}
				System.out.println(ps.toString());
			success=ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(ps.toString());
			e.printStackTrace();
		}finally {
			closeStatement(ps);
		}
		return success;
	}
	
	/**
	 * 
	 * @param columns
	 * @param values
	 * @return
	 * @throws ClassCastException
	 */
	public Long insertGetPK(String[] columns, Object[] values) throws ClassCastException{
		Long primary_key=null;
		String pk=null;
		if(insert(columns, values)==1){
			ResultSet rs=null;
			try {
				rs=DBConnection.getConnection().getMetaData().getPrimaryKeys("TABLE", null, tableName);
				if(rs.next()){
//					TABLE_CAT String => table catalog (may be null) 
//					TABLE_SCHEM String => table schema (may be null) 
//					TABLE_NAME String => table name 
//					COLUMN_NAME String => column name 
//					KEY_SEQ short => sequence number within primary key( a value of 1 represents the first column of the primary key, a value of 2 would represent the second column within the primary key). 
//					PK_NAME String => primary key name (may be null) 

					pk=rs.getString("COLUMN_NAME");
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}finally{
				closeResult(rs);
			}
			
			Object[] res=selectObject(pk, columns, values);
			if(res.length>=1)primary_key=(Long)res[0];
		}
		return primary_key;
	}

	/**
	 * @param columns
	 * @param values
	 * @param col
	 * @param key
	 * @return true if successful
	 */
	protected int updateColumn(String[] columns, Object[] values,
			String col, String key) {
		PreparedStatement ps =null;
		int success=-1;
		String ins = "UPDATE " + tableName + " SET " + columns[0] + " = ?";

		for (int i = 1; i < values.length; i++) {
			ins += ", " + columns[i] + " = ? ";
		}
		ins += " WHERE " + col + " LIKE '" + key + "';";

		ps = prepareStatement(ins);
		try {
			for (int i = 1; i <= values.length; i++) {
				setValueInPreparedStatement(ps, values[i-1], i);
			}
			success=ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeStatement(ps);
		}
		return success;
	}

	/**
	 * @param column column to be updated
	 * @param value update value
	 * @param col condition column
	 * @param key condition value
	 */
	public int updateCell(String column, Object value, String col,
			Object key) {
		return setObject(column, value, col, key);
	}

	/**
	 * Updates database cells where the value <i>key</i> equals the column
	 * <i>col</i>. The value <i>value</i> will be set in the <i>column</i>.
	 * 
	 * @param column -
	 *            column to be updatet
	 * @param value -
	 *            update value
	 * @param col -
	 *            condition column
	 * @param key -
	 *            condition value
	 */
	public int setString(String column, String value, String col, String key) {
		return setObject(column, value, col, key);
	}

	/**
	 * Updates database cells where the value <i>key</i> equals the column
	 * <i>col</i>. The value <i>value</i> will be set in the <i>column</i>.
	 * 
	 * @param column -
	 *            column to be updatet
	 * @param value -
	 *            update value
	 * @param col -
	 *            condition column
	 * @param key -
	 *            condition value
	 */
	public int setInt(String column, int value, String col, String key) {
		return setObject(column, value, col, key);
	}

	/**
	 * Updates database cells where the value <i>key</i> equals the column
	 * <i>col</i>. The value <i>value</i> will be set in the <i>column</i>.
	 * 
	 * @param column -
	 *            column to be updatet
	 * @param value -
	 *            update value
	 * @param col -
	 *            condition column
	 * @param key -
	 *            condition value
	 */
	public int setLong(String column, Long value, String col, Object key) {
		return setObject(column, value, col, key);
	}
	
	protected int setObject(String column, Object value, String col, Object key){
		int dataSet=-1;
		PreparedStatement ps = null;
		try {
			ps = prepareStatement("UPDATE ? SET ? = ? WHERE ? = ?;");
			ps.setString(1, tableName);
			ps.setString(2, column);
			setValueInPreparedStatement(ps, value, 3);
			ps.setString(4,col);
			setValueInPreparedStatement(ps, key, 5);
			dataSet=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return dataSet;
	}

	protected boolean uploadFile(String column, 
			String sFilePathAndName, String col, Object key) {
		boolean ergebnis = false;
		File              fl    = new File( sFilePathAndName );
		FileInputStream   fis   = null; 
		PreparedStatement ps = null;
		try {
			fis = new FileInputStream( fl );
			ps = prepareStatement("UPDATE ? SET ? = ? WHERE ? = ?;");
			ps.setString(1, tableName);
			ps.setString(2, column);
			ps.setBinaryStream( 3, fis, (int)fl.length() );
			ps.setString(4,col);
			setValueInPreparedStatement(ps, key, 5);
			ergebnis=ps.executeUpdate()==1;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeStatement(ps);
			try {
				if(fis!=null)fis.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return ergebnis;
	}

	/**
	 * Delete every row where the condition <i>column</i> equals <i>key</i>.
	 * 
	 * @param column -
	 *            condition column
	 * @param key -
	 *            condition value
	 */
	public int delete(String column, Object key) {
		int deleted=-1;
		PreparedStatement ps=null;
		try {
			ps = prepareStatement("DELETE FROM " + tableName
					+ " WHERE " + column + "=?;");
			setValueInPreparedStatement(ps, key, 1);
			deleted=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return deleted;
	}
	
	/**
	 * Delete every row where the condition <i>column</i> contains <i>key</i>.
	 * 
	 * @param column -
	 *            condition column
	 * @param key -
	 *            condition value
	 */
	public int deleteLike(String column, String key) {
		int deleted=-1;
		PreparedStatement ps=null;
		try {
			ps = prepareStatement("DELETE FROM " + tableName
					+ " WHERE " + column + " LIKE ?;");
			ps.setString(1, "%" + key + "%");
			deleted= ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return deleted;
	}

	/**
	 * Delete every row where the condition <i>column</i> contains NOT <i>key</i>.
	 * 
	 * @param columns -
	 *            condition column
	 * @param keys -
	 *            condition value
	 */
	public int deleteNotLike(String columns[], String keys[]) {
		int deleted=-1;
		PreparedStatement ps=null;
		try {
			if (columns.length != keys.length)
				throw new IllegalArgumentException("The two "
						+ "argument arrays have to same size!");
			String condition = " WHERE ";
			for (int i = 0; i < columns.length; i++) {
				condition += columns[i] + " NOT LIKE ?";
				if (i < columns.length - 1)
					condition += " AND ";
			}
			ps = prepareStatement("DELETE FROM " + tableName
					+ condition + ";");
			for (int i = 0, j = 1; i < columns.length; i++, j++)
				ps.setString(j, "%" + keys[i] + "%");
			deleted = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return deleted;
	}

	/**
	 * Deletes the row where column1 is key1 and column2 is key2.
	 * 
	 * @param column1
	 * @param key1
	 * @param column2
	 * @param key2
	 * @return the number of rows deleted
	 */
	public int delete(String[] columnArray, Object[] keyArray) {
		PreparedStatement ps =null;
		int deleted=-1;
		try {
			String column=getConditionAsString(columnArray);
			ps = prepareStatement("DELETE FROM " + tableName
					+ " WHERE " + column + ";");
			setValuesInStatement(ps, keyArray);
			deleted=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return deleted;
	}

	/**
	 * Deletes all database entries.
	 */
	protected int deleteAll() {
		PreparedStatement ps=null;
		int deleted=-1;
		try {
			if (size() > 0) {
				ps = prepareStatement("DELETE FROM "+ tableName + ";");
				if ((deleted=ps.executeUpdate()) >= 1)
					log.info("All Files deleted from table " + tableName);
				else
					log.info("Data could not be deleted from " + tableName
							+ "!");
			} else
				log.info("No data to be deleted in " + tableName + ".");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeStatement(ps);
		}
		return deleted;
	}

	/**
	 * Returns true if there is one or more row that fit to the condition
	 * <i>column</i> equals <i>key</i>.
	 * 
	 * @param column -
	 *            condition colum
	 * @param key -
	 *            condition value
	 * @return true if row(s) for condition exist
	 */
	public boolean existsValue(String column, Object key) {
		return countValues(new String[]{column}, new Object[]{key})>=1;
	}
	
	/**
	 * Returns true if there is one or more row that fit to the condition
	 * <i>column</i> equals <i>key</i>.
	 * 
	 * @param column -
	 *            condition colum
	 * @param key -
	 *            condition value
	 * @return true if row(s) for condition exist
	 */
	public Integer countValues(String columns[], Object[] keys) {
		Integer result=null;
		ResultSet rs = null;
		try {
			PreparedStatement ps = prepareStatement("SELECT COUNT(*) AS C FROM "
					+ tableName + getConditionAsString(columns));
			setValuesInStatement(ps, keys);
			rs = ps.executeQuery();
			if(rs.next()){
				result=rs.getInt("C");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		return result;
	}

	protected ResultSet selectOneColumnStatement(String column, String[] cols,
			Object[] keys) throws SQLException, ClassNotFoundException {
		return getSimpleSelectResults(new String[]{column}, cols, keys, new String[]{tableName}, null, null, null, null);
	}

	protected ResultSet selectStatement(String column, String col, Object key,
			String orderCol, String direc) throws SQLException, ClassNotFoundException {
		return getSimpleSelectResults(new String[]{column}, new String[]{col}, 
				new Object[]{key}, new String[]{tableName}, null, null, orderCol, direc);
	}
	
	/**
	 * Returns the first int that from <i>column</i> where the condition column
	 * <i>col</i> equals value <i>key</i>.
	 * 
	 * @param column
	 *            where the value is located
	 * @param col
	 *            column for condition
	 * @param key
	 *            value for condition
	 * @return first int from result<i>column</i>
	 */
	public Integer selectInt(String column, String col, Object key) {
		return (Integer)selectObject(column, col, key);
	}

	/**
	 * Returns the first long that from <i>column</i> where the condition
	 * column <i>col</i> equals value <i>key</i>.
	 * 
	 * @param column
	 *            where the value is located
	 * @param col
	 *            column for condition
	 * @param key
	 *            value for condition
	 * @return first long from result<i>column</i>
	 */
	public Long selectLong(String column, String col, Object key) {
		return (Long)selectObject(column, col, key);
	}
	
	public String selectString(String column, String col, Object key) {
		return (String)selectObject(column, col, key);
	}
	
	public Object[] selectObject(String column, String[] cols, Object[] keys){
		ArrayList<Object> res=null;
		Object[] result=null;
		ResultSet rs=null;
		try {
			PreparedStatement ps=prepareStatement("SELECT "+column+" FROM "+tableName+getConditionAsString(cols)+";");
			setValuesInStatement(ps, keys);
			rs=ps.executeQuery();
			res=new ArrayList<Object>();
			while(rs.next()){
				Object o=null;
				if(column==null)
					o=rs.getObject(1);
				else
					o=rs.getObject(column);
				res.add(o);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		result=res.toArray(new Object[res.size()]);
		return result;
	}
	
	protected Object selectObject(String column, String col, Object key){
		Object result=null;
		Object[] o=selectObject(column, new String[]{col}, new Object[]{key});
		if(o.length>=1)
			result=o[0];
		return result;
	}

	/**
	 * Returns the amount of datasets in this table.
	 * 
	 * @return amount of datasets in this table
	 */
	protected int size() {
		int size =-1;
		ResultSet rs =null;
		try {
			rs = prepareStatement(
					"SELECT COUNT(*) AS C FROM " + tableName + ";")
					.executeQuery();
			if(rs.next()){
				size = rs.getInt("C");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		return size;
	}

	/**
	 * Returns an Object-ArrayList of all data in <i>column</i>
	 * 
	 * @param column
	 *            with the wanted data
	 * @return Object-Vector
	 */
	protected ArrayList<Object> selectAllFrom(String column) {
		ArrayList<Object> result=new ArrayList<Object>();
		ResultSet rs =null;
		try {
			rs = prepareStatement("SELECT " + column + " FROM " + tableName + ";")
					.executeQuery();
			while (rs.next()) {
				result.add(rs.getString(column));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		return result;
	}
	
	/**
	 * Returns a {@link ResultSet} from all datasets in the table.
	 * 
	 * @return {@link ResultSet} from all datasets in the table
	 */
	protected ResultSet selectAllAsResultSet() {
		ResultSet rs=null;
		try {
			rs=getSimpleSelectResults(null, null, null, new String[]{tableName},
					null, null, null, null);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	public ArrayList<ArrayList<Object>> getSelect(String[] columns, String[] cols, Object[] keys){
		ArrayList<ArrayList<Object>> result=null;
		ResultSet rs=null;
		try {
			 rs =getSimpleSelectResults(columns, cols, keys, new String[]{tableName}, null, null, null, null);
			 result=getResultSetAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		return result;
	}
	
	public ArrayList<ArrayList<Object>> getSearchSelect(String[] columns, String[] cols, Object[] keys){
		ArrayList<ArrayList<Object>> result=null;
		ResultSet rs=null;
		try {
			 rs =getSimpleSelectResults(columns, cols,SQL_OPERATOR_LIKE ,keys, new String[]{tableName}, null, null, null,null);
			 result=getResultSetAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		return result;
	}
	
	/**
	 * Returns a single ArrayList set 
	 * @param columns
	 * @param key
	 * @return
	 */
	public ArrayList<Object> getSelectByPK(String[] columns, Object key){
		ArrayList<Object> result=null;
		ResultSet rs=null;
		try {
			String pk=getPrimaryKey();
			rs =getSimpleSelectResults(columns, new String[]{pk}, new Object[]{key}, new String[]{tableName}, null, null, null, null);
			result=getRowAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}finally{
			closeResult(rs);
		}
		return result;
	}

	protected ResultSet selectAsResultSet(String[] columns, String[] cols, Object[] keys) throws SQLException, ClassNotFoundException {
		return getSimpleSelectResults(columns, cols, keys, new String[]{tableName}, null, null, null, null);
	}

	protected void insertDefaultValues() {
	}
	
	protected String getPrimaryKey() throws SQLException{
		ResultSet rsmd=connection.getMetaData().getPrimaryKeys(null,null,tableName);
		rsmd.next();
		String pk=rsmd.getString(4);
		rsmd.close();
		return pk;
	}
	
	public String toString(){
		String columns="(";
		columns+=getAllColumns(true);
		columns+=")";
		return tableName+columns;
	}
	
	protected ArrayList<ArrayList<Object>> getReferenceTableJoin(String table1, String table2, 
			String onTable1Col,String onTable2Col,String user_id_col, Object value){
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<ArrayList<Object>> result=null;
		try {
			ps=prepareStatement("SELECT * FROM "+table1+" JOIN "+ table2+" ON "
					+onTable1Col+" = "
					+onTable2Col
					+" WHERE "+user_id_col+" = ?;");
			setValueInPreparedStatement(ps, value, 1);
			rs=ps.executeQuery();
			System.out.println(ps.toString());
			result=getResultSetAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			closeStatement(ps);
			closeResult(rs);
		}
		return result;
	}
	
	public ArrayList<ArrayList<Object>> getArrayListComplete(){
		ArrayList<ArrayList<Object>> result=null;
		ResultSet rs=selectAllAsResultSet();
		
		try {
			result=getResultSetAsArrayList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		return result;
	}
}
